153

Advanced Graphics

153

STEP 4 continued (3)

  For i =​ 1 To 100

    startrow(i) =​ 2

  Next

 

  For i =​ 5 To NumberOfCols

    j =​ j +​ 1

    thisWs.Cells(j, 1) =​ Format(fileopenWs.Cells(1, i), “MM/​DD/​YYYY”)

    For kk =​ 1 To NumberOfStates

    k =​ 1 +​ 2 * (kk -​ 1)

    thisWs.Cells(j, k +​ 2) =​ Application.SumIf(Range(Cells(2, 3),

Cells(NumberOfRows, 3)), thisWb.Sheets(“States”).Cells(kk +​ 1, 1), Range(Cells(2, i),

Cells(NumberOfRows, i)))

    If j > 2 Then

      thisWs.Cells(j, k +​ 1) =​ thisWs.Cells(j, k +​ 2) -​ thisWs.Cells(j -​ 1, k +​ 2)

    If startrow(kk) =​ 2 And thisWs.Cells(j, k +​ 2) > 0 Then

      startrow(kk) =​ j

    End If

Else 

      thisWs.Cells(1, k +​ 1) =​ thisWb.Sheets(“States”).Cells(kk +​ 1, 1) & “ Count”

      thisWs.Cells(1, k +​ 2) =​ thisWb.Sheets(“States”).Cells(kk +​ 1, 1) & “

Cum Count”

    End If

  Next

Next

STEP 5

Sort state-​based summaries in descending order.

‘find top 10

  If UserForm1.OptionButton3 =​ True Then

    For i =​ 1 To NumberOfStates

      thisWb.Sheets(“States”).Cells(i +​ 1, 5) =​ thisWb.Sheets(“States”).Cells(i +​ 1, 1)

      thisWb.Sheets(“States”).Cells(i +​ 1, 6) =​ thisWb.Sheets(“Any State”).Cells(j, 3

+​ 2 * (i -​ 1))

      thisWb.Sheets(“States”).Cells(i +​ 1, 7) =​ i +​ 1

    Next

  Else

      thisWb.Sheets(“States”).Cells(1, 6) =​ “Curr count”

For i =​ 1 To NumberOfStates

      thisWb.Sheets(“States”).Cells(i +​ 1, 5) =​ thisWb.Sheets(“States”).Cells

(i +​ 1, 1)

      thisWb.Sheets(“States”).Cells(i +​ 1, 6) =​ thisWb.Sheets(“Any State”).Cells(j, 2

+​ 2 * (i -​ 1))

      thisWb.Sheets(“States”).Cells(i +​ 1, 7) =​ i +​ 1

    Next

  End If

 

  thisWb.Worksheets(“States”).Sort.SortFields.Add2 Key:=​Range(“F2:F52”) _​

    , SortOn:=​xlSortOnValues, Order:=​xlDescending, DataOption:=​xlSortNormal